package cn.edu.nju.ws.sview.views.listener;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map.Entry;
import java.util.TimerTask;
import java.util.AbstractMap.SimpleEntry;

import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.views.data.Consts;
import cn.edu.nju.ws.sview.views.data.RecommItem;

public class RecRelationUpdateTask extends TimerTask {

	@Override
	public void run() {
		Calendar d=Calendar.getInstance();
		int day_of_week = d.get(Calendar.DAY_OF_WEEK)-1;
		
		//get the diverse items in today's visit_logs
		int h = d.get(Calendar.HOUR_OF_DAY) ;
		if(h < 22)
			return;
		
		Connection conn = DBConnectionFactory.getConnection();
		String todayRestrict = "TO_DAYS(visit_time) = " + "TO_DAYS(NOW())";
		try {
			Connection con = DBConnectionFactory.getConnection();
			
			//查看DB表clear_info看今天是否清空了
			PreparedStatement stmt = con.prepareStatement("select null from clear_info where name=? and time=?") ;
			stmt.setString(1, "rec_relation_update") ;
			java.sql.Date date = new java.sql.Date(new Date().getTime());
			stmt.setDate(2, date) ;
			ResultSet rs = stmt.executeQuery();
			boolean flag = false;
			/*if(rs.next())
				flag = true;*/
			rs.close();
			stmt.close();
			
			if(!flag){
				ArrayList<RecommItem> items = new ArrayList<RecommItem>();
				PreparedStatement it_ps = conn.prepareStatement(
					"SELECT distinct item_id, item_type, lang FROM visit_logs WHERE view_id = -1 " +
					"AND event_type = 'visit' AND " + todayRestrict);
				ResultSet rs0 = it_ps.executeQuery();
				while(rs0.next()){
					int id = rs0.getInt("item_id");
					int type = rs0.getInt("item_type");
					String lang = rs0.getString("lang");
					items.add(new RecommItem(id, type, lang));
				}
				rs0.close();
				it_ps.close();
			
				HashMap<RecommItem, ArrayList<RecommItem>> map = new HashMap<RecommItem, ArrayList<RecommItem>>();
				for(RecommItem re : items){
					int itemId = re.getItemId();
					int itemType = re.getItemType();
					String lang = re.getLang();
				
					String typeRestrict = "";
					//如果当前的item为1，2，则可以推荐类型为1、2的items。如果是dataset（值为uri），则限定推荐的类型为uri.如果是3的话，只推荐3.
					if(itemType == 1 || itemType == 2)
						typeRestrict += "item_type in (1, 2) ";
					else if(itemType == 11 || itemType == 12)
						typeRestrict += "item_type in (11, 12) ";
					else
						typeRestrict += "item_type = " + itemType + " ";
				
				//第一次查询
					ArrayList<Entry<Integer, String>> relatedUsers = new ArrayList<Entry<Integer, String>>();
					PreparedStatement ps1 = conn.
	    	        prepareStatement("SELECT user_id, visit_time FROM visit_logs WHERE " +
	    	        		"view_id = -1 AND item_id = ? AND item_type = ? AND " + 
	    	        		todayRestrict + " AND event_type = 'visit';");
					ps1.setInt(1, itemId);
					ps1.setInt(2, itemType);
				
					ResultSet rs1 = ps1.executeQuery();
					while(rs1.next()){
						int uId = rs1.getInt("user_id");
						String vTime = rs1.getDate("visit_time").toString() + " " + rs1.getTime("visit_time").toString();
						relatedUsers.add(new SimpleEntry<Integer, String>(uId, vTime));
					}
					rs1.close();
					ps1.close();
				
					//第二次查询，对于每一个用户的每次访问，查询最近10min中该用户访问的该语言环境下的item，加入到中间结果中
					ArrayList<RecommItem> recs = new ArrayList<RecommItem>();
					for(Entry<Integer, String> en : relatedUsers){
						int uId = en.getKey().intValue();
						String vTime = en.getValue();
						String query = "SELECT DISTINCT item_id, item_type FROM visit_logs WHERE user_id = " +
							"? AND lang = ? AND view_id = -1 AND (visit_time BETWEEN " +
							"? AND DATE_ADD(?, INTERVAL ? MINUTE)) AND event_type = 'visit' AND "
							+ typeRestrict + ";";
						PreparedStatement ps2 = conn.prepareStatement(query);
						ps2.setInt(1, uId);
						ps2.setString(2, lang);
						ps2.setString(3, vTime);
						ps2.setString(4, vTime);
						ps2.setInt(5, Consts.VIEW_TIME_SPAN);
						ResultSet rs2 = ps2.executeQuery();
						while(rs2.next()){
							int iId = rs2.getInt("item_id");
							int iType = rs2.getInt("item_type");
							RecommItem temp = new RecommItem(iId, iType);        
							if(iId != itemId && iType != itemType){
								recs.add(temp);
							}
						}
						ps2.close();
						rs2.close();
					}
					map.put(re, recs);
				}
				String sql = "INSERT INTO rec_relation (item_id, item_type, rec_id, rec_type, day_related_times, date) " +
					"VALUES(?,?,?,?,?," + day_of_week + ") on duplicate key update day_related_times = day_related_times + 1;";
				PreparedStatement ps3 = conn.prepareStatement(sql);
				conn.setAutoCommit(false);
				try{
					for(RecommItem item : map.keySet()){
						ArrayList<RecommItem> temp = map.get(item);
						for(RecommItem recItem : temp){
							ps3.setInt(1, item.getItemId());
							ps3.setString(2, "" + item.getItemType());
							ps3.setInt(3, recItem.getItemId());
							ps3.setString(4, "" + recItem.getItemType());
							ps3.setInt(5, 1);
							ps3.executeUpdate();
							conn.commit();
						}
					}
				}finally{
					conn.setAutoCommit(true);
					ps3.close();
				}
				PreparedStatement del_ps = conn.prepareStatement(
					"DELETE FROM rec_relation WHERE date = ?");
				if(day_of_week == 6)
					day_of_week = -1;
				del_ps.setInt(1, day_of_week + 1);
				del_ps.executeUpdate();
				del_ps.close();
				PreparedStatement insertStmt = con.prepareStatement("insert into clear_info values(?,?) on duplicate key update time=values(time)") ;
				insertStmt.setString(1, "rec_relation_update");
				insertStmt.setDate(2, new java.sql.Date(new Date().getTime())) ;
				insertStmt.executeUpdate();
				insertStmt.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try{
				conn.close();
			}catch(SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}  
public static void main(String[] args) throws SQLException{/*
	Connection conn = DBConnectionFactory.getConnection();
	String sql = "INSERT INTO rec_relation (item_id, item_type, rec_id, rec_type, day_related_times, date) " +
	"VALUES(?,?,?,?,?," + 1 + ") on duplicate key update day_related_times = day_related_times + 1;";
	PreparedStatement ps3 = conn.prepareStatement(sql);
	conn.setAutoCommit(false);
	try{
	for(int i = 0; i < 5; i++){
			ps3.setInt(1, 56 + i);
			ps3.setString(2, "1");
			ps3.setInt(3, 54 + i);
			ps3.setString(4, "1");
			ps3.setInt(5, 1);
			ps3.executeUpdate();
			conn.commit();
		
	}
}finally{
	conn.setAutoCommit(true);
	ps3.close();
}*/
	RecRelationUpdateTask t = new RecRelationUpdateTask();
	t.run();

}
}
